package com.telecom.cms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.util.Constant;
import com.telecom.cms.dto.*;

import com.telecom.cms.forms.ModifyatomForm;

public class SEG_BIZ_ATOM_DEFDAO {

	private static List instlist = null;

	private static final String SQL_LIST = "select * from "
			+ Constant.SEG_BIZ_ATOM_DEF + " order by 1";

	private static final String SQL_FINDALL = "select * from "
			+ Constant.SEG_BIZ_ATOM_DEF
			+ " where SHOW_TYPE_ID in (200001,2,3,100001) order by 1";

	private static final String SQL_FINDBYID = "select * from "
			+ Constant.SEG_BIZ_ATOM_DEF + " where BIZ_ATOM_ID = ? ";

	private static final String SQL_FINDTABNAME = "select distinct "
			+ Constant.SEG_BIZ_ATOM_DEF + ".BAS_TABLE_NAME from "
			+ Constant.SEG_BIZ_ATOM_DEF + " order by 1";

	private static final String SQL_FINDBYNAME = "select * from "
			+ Constant.SEG_BIZ_ATOM_DEF + " where BIZ_ATOM_NAME = ? ";

	private static final String SQL_INSERT = "insert into "
			+ Constant.SEG_BIZ_ATOM_DEF
			+ " (BIZ_ATOM_ID,BIZ_ATOM_NAME,VALUE_TYPE_ID,SHOW_TYPE_ID,CDE_SRC_TABLE_NAME,BAS_TABLE_NAME,BAS_TABLE_COL_NAME,BAS_TABLE_COL_TYPE,IS_USERD,ATOM_KIND_ID,USER_ID,CREATE_TIME,BIZ_ATOM_DESC,BIZ_ATOM_FLAG,SRC_TABLE_COL_NAME)Values(?,?,?,?,?,?,?,?,'1',?,,,?,?,?)";

	private static final String SQL_INSERTINTOREL = "Insert  Into "
			+ Constant.SEG_ATOM_GROUP_REL + " Values(?,?)";

	private static final String SQL_DELETEBYID = "delete from "
			+ Constant.SEG_BIZ_ATOM_DEF + " where BIZ_ATOM_ID = ?";

	private static final String SQL_DELETEREL = "delete from "
			+ Constant.SEG_ATOM_GROUP_REL + " where BIZ_ATOM_ID = ?";

	private static final String SQL_DELETERELONGROUP = "delete from "
			+ Constant.SEG_ATOM_GROUP_REL
			+ " where BIZ_ATOM_ID = ? ";

	private static final String SQL_MODIFY = "update "
			+ Constant.SEG_BIZ_ATOM_DEF
			+ " set BIZ_ATOM_NAME=?,VALUE_TYPE_ID=?,SHOW_TYPE_ID=?,CDE_SRC_TABLE_NAME=?,BAS_TABLE_NAME=?,BAS_TABLE_COL_NAME=?,BAS_TABLE_COL_TYPE=?,ATOM_KIND_ID=?,BIZ_ATOM_DESC=?,BIZ_ATOM_FLAG=?,SRC_TABLE_COL_NAME=? where BIZ_ATOM_ID = ?";

	private static final String SQL_MODIFYREL = "update "
			+ Constant.SEG_ATOM_GROUP_REL
			+ " set BIZ_ATOM_ID=?,ATOM_GROUP_ID=? where BIZ_ATOM_ID=? and ATOM_GROUP_ID=?";

	private static final String SQL_INSERTREL = "insert into "
			+ Constant.SEG_ATOM_GROUP_REL
			+ " values (BIZ_ATOM_ID=?,ATOM_GROUP_ID=?)";

	private Connection conn = null;

	public SEG_BIZ_ATOM_DEFDAO(Connection con) {

		this.conn = con;

	}

	public SEG_BIZ_ATOM_DEFDAO() {

	}

	Dao dao = new DaoImp();

	public List findATOMAll() {

		ResultSet rs = null;
		List list = new ArrayList();
		try {

			rs = dao.select(SQL_FINDALL);
			while (rs.next()) {
				SEG_BIZ_ATOM_DEFDTO seg_BIZ_ATOM_DEFDTO = new SEG_BIZ_ATOM_DEFDTO();
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_ID(rs.getInt(1));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_NAME(rs.getString(2));

				seg_BIZ_ATOM_DEFDTO.setVALUE_TYPE_ID(rs.getInt(3));
				seg_BIZ_ATOM_DEFDTO.setSHOW_TYPE_ID(rs.getInt(4));
				seg_BIZ_ATOM_DEFDTO.setCDE_SRC_TABLE_NAME(rs.getString(5));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_NAME(rs.getString(6));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_COL_NAME(rs.getString(7));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_COL_TYPE(rs.getString(8));
				seg_BIZ_ATOM_DEFDTO.setIS_USERD(rs.getString(9));
				seg_BIZ_ATOM_DEFDTO.setATOM_KIND_ID(rs.getInt(10));
				seg_BIZ_ATOM_DEFDTO.setUSER_ID(rs.getString(11));
				seg_BIZ_ATOM_DEFDTO.setCREATE_TIME(rs.getTimestamp(12));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_DESC(rs.getString(13));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_FLAG(rs.getString(14));
				seg_BIZ_ATOM_DEFDTO.setSRC_TABLE_COL_NAME(rs.getString(15));
				list.add(seg_BIZ_ATOM_DEFDTO);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public List findAll() {

		if (instlist != null)
			return instlist;
		findAllForce();
		return instlist;
	}

	public List findAllForce() {

		ResultSet rs = null;
		List list = new ArrayList();
		try {
			rs = dao.select(SQL_LIST);
			while (rs.next()) {
				SEG_BIZ_ATOM_DEFDTO seg_BIZ_ATOM_DEFDTO = new SEG_BIZ_ATOM_DEFDTO();
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_ID(rs.getInt(1));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_NAME(rs.getString(2));

				seg_BIZ_ATOM_DEFDTO.setVALUE_TYPE_ID(rs.getInt(3));
				seg_BIZ_ATOM_DEFDTO.setSHOW_TYPE_ID(rs.getInt(4));
				seg_BIZ_ATOM_DEFDTO.setCDE_SRC_TABLE_NAME(rs.getString(5));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_NAME(rs.getString(6));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_COL_NAME(rs.getString(7));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_COL_TYPE(rs.getString(8));
				seg_BIZ_ATOM_DEFDTO.setIS_USERD(rs.getString(9));
				seg_BIZ_ATOM_DEFDTO.setATOM_KIND_ID(rs.getInt(10));
				seg_BIZ_ATOM_DEFDTO.setUSER_ID(rs.getString(11));
				seg_BIZ_ATOM_DEFDTO.setCREATE_TIME(rs.getTimestamp(12));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_DESC(rs.getString(13));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_FLAG(rs.getString(14));
				seg_BIZ_ATOM_DEFDTO.setSRC_TABLE_COL_NAME(rs.getString(15));
				list.add(seg_BIZ_ATOM_DEFDTO);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		instlist = list;
		return list;
	}

	public List findAll(String clutype) {//get atoms by cluster type
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			rs = dao.select("select * from " + Constant.SEG_BIZ_ATOM_DEF
					+ " where atom_kind_id in(" + clutype + ",3) order by 1");
			while (rs.next()) {
				SEG_BIZ_ATOM_DEFDTO seg_BIZ_ATOM_DEFDTO = new SEG_BIZ_ATOM_DEFDTO();
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_ID(rs.getInt(1));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_NAME(rs.getString(2));
				seg_BIZ_ATOM_DEFDTO.setVALUE_TYPE_ID(rs.getInt(3));
				seg_BIZ_ATOM_DEFDTO.setSHOW_TYPE_ID(rs.getInt(4));
				seg_BIZ_ATOM_DEFDTO.setCDE_SRC_TABLE_NAME(rs.getString(5));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_NAME(rs.getString(6));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_COL_NAME(rs.getString(7));
				seg_BIZ_ATOM_DEFDTO.setBAS_TABLE_COL_TYPE(rs.getString(8));
				seg_BIZ_ATOM_DEFDTO.setIS_USERD(rs.getString(9));
				seg_BIZ_ATOM_DEFDTO.setATOM_KIND_ID(rs.getInt(10));
				seg_BIZ_ATOM_DEFDTO.setUSER_ID(rs.getString(11));
				seg_BIZ_ATOM_DEFDTO.setCREATE_TIME(rs.getTimestamp(12));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_DESC(rs.getString(13));
				seg_BIZ_ATOM_DEFDTO.setBIZ_ATOM_FLAG(rs.getString(14));
				seg_BIZ_ATOM_DEFDTO.setSRC_TABLE_COL_NAME(rs.getString(15));
				list.add(seg_BIZ_ATOM_DEFDTO);
			}

			if (rs != null) {

				rs.close();
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			dao.closeconn();
		}
		return list;
	}

	public List findTabName() {

		PreparedStatement ps = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			ps = conn.prepareStatement(SQL_FINDTABNAME);
			rs = ps.executeQuery();
			while (rs.next()) {
				String name = rs.getString(1);
				if (!name.equals("") && !name.equals("null") && name != null) {
					list.add(name);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;

	}

	public SEG_BIZ_ATOM_DEFDTO findById(int id) {
		if (null == instlist)
			findAll();
		for (int i = 0; i < instlist.size(); i++)
			if (((SEG_BIZ_ATOM_DEFDTO) instlist.get(i)).getBIZ_ATOM_ID() == id)
				return (SEG_BIZ_ATOM_DEFDTO) instlist.get(i);
		findAllForce();
		for (int i = 0; i < instlist.size(); i++)
			if (((SEG_BIZ_ATOM_DEFDTO) instlist.get(i)).getBIZ_ATOM_ID() == id)
				return (SEG_BIZ_ATOM_DEFDTO) instlist.get(i);
		/*
		 * PreparedStatement ps = null; ResultSet rs = null;
		 * SEG_BIZ_ATOM_DEF_NEWDTO dto = new SEG_BIZ_ATOM_DEF_NEWDTO(); try { ps =
		 * conn.prepareStatement(SQL_FINDBYID); ps.setInt(1, id); rs =
		 * ps.executeQuery(); if (rs.next()) {
		 * 
		 * dto.setBIZ_ATOM_ID(rs.getInt(1));
		 * dto.setBIZ_ATOM_NAME(rs.getString(2));
		 * 
		 * dto.setVALUE_TYPE_ID(rs.getInt(3));
		 * dto.setSHOW_TYPE_ID(rs.getInt(4));
		 * dto.setCDE_SRC_TABLE_NAME(rs.getString(5));
		 * dto.setBAS_TABLE_NAME(rs.getString(6));
		 * dto.setBAS_TABLE_COL_NAME(rs.getString(7));
		 * dto.setBAS_TABLE_COL_TYPE(rs.getString(8));
		 * dto.setIS_USERD(rs.getString(9)); dto.setATOM_KIND_ID(rs.getInt(10));
		 * dto.setUSER_ID(rs.getString(11));
		 * dto.setCREATE_TIME(rs.getTimestamp(12));
		 * dto.setBIZ_ATOM_DESC(rs.getString(13));
		 * dto.setBIZ_ATOM_FLAG(rs.getString(14));
		 * dto.setSRC_TABLE_COL_NAME(rs.getString(15)); } } catch (SQLException
		 * e) { // TODO Auto-generated catch block e.printStackTrace(); }
		 * finally { try { if (rs != null) {
		 * 
		 * rs.close(); } if (ps != null) { ps.close(); } } catch (SQLException
		 * e) { // TODO Auto-generated catch block e.printStackTrace(); } }
		 */
		return null;
	}

	public SEG_BIZ_ATOM_DEFDTO findByName(String name) {
		if (null == instlist)
			findAll();
		for (int i = 0; i < instlist.size(); i++){
			SEG_BIZ_ATOM_DEFDTO atom = (SEG_BIZ_ATOM_DEFDTO)instlist.get(i);
			if (atom.getBIZ_ATOM_NAME().trim().equals(name.trim())){
				if (atom.getATOM_KIND_ID() == 1)
					return atom;
			}
		}
		/*
		 * PreparedStatement ps = null; ResultSet rs = null;
		 * SEG_BIZ_ATOM_DEF_NEWDTO dto = new SEG_BIZ_ATOM_DEF_NEWDTO(); try { ps =
		 * conn.prepareStatement(SQL_FINDBYNAME); ps.setString(1, name); rs =
		 * ps.executeQuery(); if (rs.next()) {
		 * 
		 * dto.setBIZ_ATOM_ID(rs.getInt(1));
		 * dto.setBIZ_ATOM_NAME(rs.getString(2));
		 * 
		 * dto.setVALUE_TYPE_ID(rs.getInt(3));
		 * dto.setSHOW_TYPE_ID(rs.getInt(4));
		 * dto.setCDE_SRC_TABLE_NAME(rs.getString(5));
		 * dto.setBAS_TABLE_NAME(rs.getString(6));
		 * dto.setBAS_TABLE_COL_NAME(rs.getString(7));
		 * dto.setBAS_TABLE_COL_TYPE(rs.getString(8));
		 * dto.setIS_USERD(rs.getString(9)); dto.setATOM_KIND_ID(rs.getInt(10));
		 * dto.setUSER_ID(rs.getString(11));
		 * dto.setCREATE_TIME(rs.getTimestamp(12));
		 * dto.setBIZ_ATOM_DESC(rs.getString(13));
		 * dto.setBIZ_ATOM_FLAG(rs.getString(14));
		 * dto.setSRC_TABLE_COL_NAME(rs.getString(15)); } } catch (SQLException
		 * e) { // TODO Auto-generated catch block e.printStackTrace(); }
		 * finally { try { if (rs != null) {
		 * 
		 * rs.close(); } if (ps != null) { ps.close(); } } catch (SQLException
		 * e) { // TODO Auto-generated catch block e.printStackTrace(); } }
		 */
		return null;
	}

	public void genCustInfo(SEG_BIZ_ATOM_DEFDTO seg, int campid,
			String campseqid, String clutype, int showareaid, String latncd,
			String latnid) {
		String idtype = (clutype.equals("1") ? "prd_inst_id" : "cust_id");
		String sql = null;
		try {
			// insert base info such as accs_nbr,name
			// sql="";
			// dao.insert(sql);

			if (null == seg.getSRC_TABLE_COL_NAME()
					|| seg.getSRC_TABLE_COL_NAME().equals("null")
					|| seg.getSRC_TABLE_COL_NAME().equals(""))
				sql = "insert into "
						//+ Constant.camp_cust_info
						+ Constant.getStPara("DATABASE") + ".CAMP_CUST_INFO"
						+ "  select '"
						+ latnid
						+ "',"
						+ campid
						+ ","
						+ campseqid
						+ ",c.cust_id,c.prd_inst_id,"
						+ showareaid
						+ ","
						+ seg.getBIZ_ATOM_ID()
						+ ",a."
						+ seg.getBAS_TABLE_COL_NAME().trim()
						+ (seg.getBIZ_ATOM_FLAG().equals("money") ? "/100"
								: (seg.getBIZ_ATOM_FLAG().equals("time") ? "/60"
										: " "))
						+ ","
						+ ((new DaoImp())
								.checksql("select billing_cycle_id from "
									+ (new Constant()).changeLatnId(latncd, seg.getBAS_TABLE_NAME())) ? "cast(a.billing_cycle_id as char(6))"
								: ("''")) + " from " + seg.getBAS_TABLE_NAME()
						+ " a " + "inner join " + Constant.getStPara("DATABASE") + ".CAMP_CUST_APPD_RSLT" //Constant.CAMP_CUST_APPD_RSLT
						+ " c  on   a." + idtype + "=c." + idtype
						+ " and c.camp_id=" + campid + " and c.camp_seq_id="
						+ campseqid + "";
			else
				sql = "insert into "
						//+ Constant.camp_cust_info
						+ Constant.getStPara("DATABASE") + ".CAMP_CUST_INFO"
						+ "  select '"
						+ latnid
						+ "',"
						+ campid
						+ ","
						+ campseqid
						+ ",c.cust_id,c.prd_inst_id,"
						+ showareaid
						+ ","
						+ seg.getBIZ_ATOM_ID()
						+ ",b."
						+ seg.getSRC_TABLE_COL_NAME()
						+ ","
						+ ((new DaoImp())
								.checksql("select billing_cycle_id from "
									+ (new Constant()).changeLatnId(latncd, seg.getBAS_TABLE_NAME())) ? "cast(a.billing_cycle_id as char(6))"
								: ("''")) + " from " + seg.getBAS_TABLE_NAME()
						+ " a " + "inner join " + seg.getCDE_SRC_TABLE_NAME()
						+ " b " + "on a." + seg.getBAS_TABLE_COL_NAME().trim()
						+ "=b." + seg.getBAS_TABLE_COL_NAME().trim() + " "
						+ "inner join " + Constant.getStPara("DATABASE") + ".CAMP_CUST_APPD_RSLT" //Constant.CAMP_CUST_APPD_RSLT
						+ "  c  on   a." + idtype + "=c." + idtype
						+ " and c.camp_id=" + campid + " and c.camp_seq_id="
						+ campseqid + "";

			sql = (new Constant()).changeLatnId(latncd, sql);
			dao.insert(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void insert(ModifyatomForm modifyatomForm) {
		PreparedStatement psmt = null;
		ResultSet rs = null;
		try {
			conn.setAutoCommit(false);
			// 插入原子表
			psmt = conn.prepareStatement(SQL_INSERT);
			psmt.setInt(1, Integer.parseInt(modifyatomForm.getBIZ_ID()));
			psmt.setString(2, modifyatomForm.getName());
			psmt.setInt(3, Integer.parseInt(modifyatomForm.getVALUE_TYPE_ID()));
			psmt.setInt(4, Integer.parseInt(modifyatomForm.getATOM_KIND_ID()));
			psmt.setString(5, modifyatomForm.getCDE_SRC_TABLE_NAME());
			psmt.setString(6, modifyatomForm.getBAS_TABLE_NAME());
			psmt.setString(7, modifyatomForm.getBAS_TABLE_COL_NAME());
			psmt.setString(8, modifyatomForm.getBAS_TABLE_COL_TYPE());
			psmt.setInt(9, Integer.parseInt(modifyatomForm.getATOM_KIND_ID()));
			psmt.setString(10, modifyatomForm.getBIZ_DESC());
			psmt.setString(11, modifyatomForm.getBIZ_ATOM_FLAG());
			psmt.setString(12, modifyatomForm.getSRC_TABLE_COL_NAME());
			psmt.executeUpdate();
			// 取得id
			int atom_id = Integer.parseInt(modifyatomForm.getBIZ_ID());
			// 插入关系表
			if (!modifyatomForm.getPrename().trim().equals("")){
				psmt = conn.prepareStatement(SQL_INSERTINTOREL);
				psmt.setInt(1, atom_id);
				psmt.setInt(2, Integer.parseInt(modifyatomForm.getPrename()));
				psmt.executeUpdate();
			}
			conn.commit();
			instlist = null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
				e.printStackTrace();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {

				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void deleteById(String[] id) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(SQL_DELETEBYID);
			for (int i = 0; i < id.length; i++) {
				ps.setInt(1, Integer.parseInt(id[i]));
				ps.executeUpdate();
				ps = conn.prepareStatement(SQL_DELETEREL);
				ps.setInt(1, Integer.parseInt(id[i]));
				ps.executeUpdate();
				conn.commit();
			}
			instlist = null;
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (ps != null) {
					ps.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			}
		}
	}

	public void modifyById(int id, ModifyatomForm modifyatomForm) {
		PreparedStatement psmt = null;

		try {
			conn.setAutoCommit(false);
			psmt = conn.prepareStatement(SQL_MODIFY);
			psmt.setString(1, modifyatomForm.getName());
			psmt.setInt(2, Integer.parseInt(modifyatomForm.getVALUE_TYPE_ID()));
			psmt.setInt(3, Integer.parseInt(modifyatomForm.getSHOW_TYPE_ID()));
			psmt.setString(4, modifyatomForm.getCDE_SRC_TABLE_NAME());
			psmt.setString(5, modifyatomForm.getBAS_TABLE_NAME());
			psmt.setString(6, modifyatomForm.getBAS_TABLE_COL_NAME());
			psmt.setString(7, modifyatomForm.getBAS_TABLE_COL_TYPE());
			psmt.setInt(8, Integer.parseInt(modifyatomForm.getATOM_KIND_ID()));
			psmt.setString(9, modifyatomForm.getBIZ_DESC());
			psmt.setString(10, modifyatomForm.getBIZ_ATOM_FLAG());
			psmt.setString(11, modifyatomForm.getSRC_TABLE_COL_NAME());
			psmt.setInt(12, id);
			psmt.executeUpdate();
			
			String pre = modifyatomForm.getPrename();
			psmt = conn.prepareStatement(SQL_DELETERELONGROUP);
			psmt.setInt(1, id);
			psmt.executeUpdate();
			if (!pre.trim().equals("")){
				psmt = conn.prepareStatement(SQL_INSERTREL);
				psmt.setInt(1, id);
				psmt.setInt(2, Integer.parseInt(pre));
				psmt.executeUpdate();
			}
			
			conn.commit();
			instlist = null;
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			e.printStackTrace();

		} finally {
			try {
				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();

			}
		}
	}

}